>I've been struggling with this problem for a while now and I can't seem to
>find a solution. I have a postal-code database, currently populated with over
>76,000 United States ZIP codes. Each record contains, among other things, the
>latitude and longitude for the postal code. I have a stored procedure that
>calculates the distance between any two points on the globe. I'm trying to
>figure out a fast way to locate all of the postal codes within an arbitrary
>radius of another postal code.
>
>The brute force method requires a sequential scan of all 76,000 records
>looking for those that fall within the specified area. A more
>high-performance method would be to pre-calculate the distances between all
>postal codes (possibly limiting the distance to save space). However, this
>requires more than 76,000 ^ 2 database operations. On a 1 GHz box, I
>calculated this would take nearly one year complete. It would take twice as
>long if I wanted to create a second cache for city/state searches.
>
>Does anybody have and tips on solving this issue? Is there any sort of
>complex index I could create based on the results of an arbitrary stored
>procedure call? Maybe some custom C code?
>
>--
>Milo Hyson
>CyberLife Labs, LLC
>
Depending upon your application, an alternate way to do the
precalculation is to set discrete radii - say 1 mile, 5 miles, 10
miles - and then calculate for each zip code what other zip codes are
within that radius. It may not be faster to calculate, but it is
better for retrieval speed. If you are only interested in smaller
radii, it will be faster because you will not have to compute
distances between zip codes in CA and zip codes in NY.
However, you must assume with this method that your customer will not
change his mind regularly about what radii should be available in the
interface. ;-)
Elaine Lindelef
Cognitivity
